# # 设计一个留言本的表（ID，留言内容，留言人，留言时间，是否删除）（表名，和字段名自己设计成英文：注意，不要用中文，用中文的直接0分）；
# #    使用PyMySQL 驱动模块，实现对这个表的增加，删除，修改，查询；数据库操作需要加入异常处理逻辑；

import pymysql

db = pymysql.connect("localhost","testuser","w406833650","test" )

cursor = db.cursor()


def add():
    id = input("ID:")
    message = input("message:")
    user = input("user:")
    time = input("time:")
    dele = input("delete:")
    sql = "INSERT INTO test(ID,message,user,time,delete)VALUES(\"" + id + "\",\"" + message + "\",\"" + user + "\",\"" + time + "\",\"" + int(dele) + "\")"
    print(sql)

    try:
        cursor.execute(sql)
        db.commit()
    except:
        db.rollback()


def search():
    c = input("The part you want to search:")
    z = input("message:")
    sql = "SELECT * FROM test WHERE " + c + " = \'" + z + "\'"

    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        for row in results:
            id = row[0]
            message = row[1]
            user = row[2]
            time = row[3]
            dele = row[4]

            print("id = %s, message = %s, user = %s, time = %s, delete = %s" % (id, message, user, time, dele))
    except:
        print("Error")


def change():
    cy = input("The part you want to change:")
    zy = input("Mseeage before you change:")
    zg = input("What you want to change:")
    sql = "UPDATE test SET " + cy + " = \'" + zg + "\' WHERE "+ cy + " = \'" + zy + "\'"

    try:
        cursor.execute(sql)
        db.commit()
    except:
        db.rollback()


def delete():
    c = input("The part you want to delete:")
    z = input("mseeage:")
    sql = "DELETE FROM test WHERE " + c + "= \'" + z + "\'"

    try:
        cursor.execute(sql)
        db.commit()
    except:
        db.commit()


while True:
    print("Please input your operation:")
    operation = input()

    if operation == "add":
        add()
    elif operation == "search":
        search()
    elif operation == "change":
        change()
    elif operation == "delete":
        delete()
    elif operation == "over":
        break

db.close()